﻿CREATE FUNCTION [acms].[GetPostParentPath] ( @PostId int, @PublishDate DateTime)
RETURNS VARCHAR(1000)
AS
BEGIN
	
	declare @ParentId int, @ParentName varchar(1000), @Name varchar(100)

	Select @ParentId = c.Id, @ParentName = c.Name, @Name = a.Name
	from acms.Posts a
		inner join acms.PostPositions b on a.Id = b.PostId and (b.PublishDate = @PublishDate or (b.PublishDate is null and @PublishDate is null))
		inner join acms.Posts c on a.ParentGuid = c.PostGuid
		inner join acms.PostPositions d on c.Id = d.PostId and (d.PublishDate = @PublishDate or (d.PublishDate is null and @PublishDate is null))
	where a.Id = @PostId
	
	if(@ParentId is null)
		set @ParentName = null
	else
		set @ParentName = isNull(acms.GetPostParentPath(@ParentId, @PublishDate), '') + @ParentName + '/'
	return @ParentName
END


